import pg from 'pg';
const { Pool } = pg;

// PostgreSQL连接池配置
const dbPoolConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT) || 5432,
  database: process.env.DB_NAME || 'bike_wholesale',
  user: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD || 'password',
  
  // 连接池配置
  max: 5,
  min: 1,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 10000,
  acquireTimeoutMillis: 30000,
  
  ssl: process.env.DB_SSL === 'true' ? { rejectUnauthorized: false } : false
};

const dbPool = new Pool(dbPoolConfig);

// 连接池错误处理
dbPool.on('error', (err) => {
  console.warn('⚠️ 数据库连接池错误:', err.message);
});

// 测试数据库连接
const testConnection = async () => {
    try {
        const client = await dbPool.connect();
        const result = await client.query('SELECT 1');
        client.release();
        console.log('✅ 用户端数据库连接成功');
        return true;
    } catch (error) {
        console.warn('⚠️ 用户端数据库连接失败:', error.message);
        return false;
    }
};

// 查询函数
const query = async (text, params) => {
    try {
        const result = await dbPool.query(text, params);
        return result;
    } catch (error) {
        console.error('数据库查询错误:', error);
        throw error;
    }
};

// 事务处理
const transaction = async (callback) => {
    const client = await dbPool.connect();
    try {
        await client.query('BEGIN');
        const result = await callback(client);
        await client.query('COMMIT');
        return result;
    } catch (error) {
        await client.query('ROLLBACK');
        throw error;
    } finally {
        client.release();
    }
};

export {
    dbPool,
    query,
    transaction,
    testConnection
};